"""数据库 模块"""
from typing import List
from django.db import connection

class DBUtils:
    @classmethod
    def get_connection(cls):
        return connection

    @classmethod
    def insert(cls, sql, args=None):
        """插入数据, 返回主键"""
        # 获取 数据库的连接
        conn = cls.get_connection()
        # 获取SQL执行器
        with conn.cursor() as cursor:
            cursor.execute(sql, args)
            # 返回主键
            return cursor.lastrowid

    @classmethod
    def update(cls, sql, args=None):
        """更新数据, 返回影响行数"""
        # 获取 数据库的连接
        conn = cls.get_connection()
        # 获取SQL执行器
        with conn.cursor() as cursor:
            row = cursor.execute(sql, args)
            # 返回影响行数
            return row

    @classmethod
    def delete(cls, sql, args=None):
        """删除数据 返回影响行数"""
        return cls.update(sql, args)

    @classmethod
    def select_one(cls, sql, args=None) -> dict:
        """查询数据、 并返回一个字典"""
        # 获取数据库连接
        conn = cls.get_connection()
        with conn.cursor() as cursor:
            # 执行SQL、 并获取影响行数
            row = cursor.execute(sql, args)
            assert row <= 1, f"select 期待返回1条数据，实际返回{row}条数据"
            data = cursor.fetchone()
            if data is not None:
                columns = [col[0] for col in cursor.description]
                return dict(zip(columns, data))

    @classmethod
    def select_all(cls, sql, args=None) -> List[dict]:
        """查询数据、 并返回一个字典"""
        conn = cls.get_connection()
        with conn.cursor() as cursor:
            # 执行SQL、 并获取影响行数
            row = cursor.execute(sql, args)
            assert row >= 0, f"select返回{row}条数据"
            data = cursor.fetchall()
            if data is not None:
                columns = [col[0] for col in cursor.description]
                return [dict(zip(columns, d)) for d in data]

    @classmethod
    def execute(cls, sql, args=None, *, functional):
        """执行任意 SQL、并返回满足条件的结果"""
        # 获取 数据库的连接
        conn = cls.get_connection()
        # 获取SQL执行器
        with conn.cursor() as cursor:
            cursor.execute(sql, args)
            return functional(cursor)